package sc.rawdata;


import java.text.*;
import java.util.*;

import org.apache.http.client.*;
import org.apache.http.client.methods.HttpGet;

import org.apache.http.impl.client.BasicResponseHandler;
import org.apache.http.impl.client.DefaultHttpClient;

import org.hibernate.*;

import org.json.*;

import sc.hibernate.config.HibernateSession;



/**
 * 
 * 
 * @author Cédric Rais
 *
 */
public class GetRawData {

	//Date format to convert YQL to MySQL
	private static SimpleDateFormat outputDateFormat = new SimpleDateFormat("yyyy-MM-dd");
	
	/**
	 * Get the industry data and symbol per industry
	 */
	public void getIndustries(){
		//Generate sector's ids list
		String response = executeYQL("http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.sectors&format=json&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys");
		String industryIds = "";
		try{
	    	JSONObject json = new JSONObject(response);
	    	JSONObject query = json.getJSONObject("query"); 
	    	JSONObject results = query.getJSONObject("results");
	    	JSONArray markets = results.getJSONArray("sector");
	    	for (int i = 0; i < markets.length(); i++) {
	    		JSONObject market = markets.getJSONObject(i); 
	    		JSONArray sectors;
	    		//there is only one sector with id 2xx so its not a JSONArray,
	    		//add it manually to the list
	    		if(i==1){
	    			industryIds = industryIds + "210,";
	    			continue;
	    		}else
	    			sectors = market.getJSONArray("industry");
	    		for (int j = 0; j < sectors.length(); j++) { 
	    			JSONObject sector = sectors.getJSONObject(j); 
	    			industryIds = industryIds + sector.get("id")+",";
	    		}
	    	}
	    }catch(JSONException je){
	    	je.printStackTrace();
	    	return;
	    }
		industryIds = industryIds.substring(0, industryIds.length()-1);

		//Get data from Yahoo
	    response = executeYQL("http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.industry%20where%20id%20in%20("+ industryIds +")&format=json&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys");
	    ArrayList<String> symbols = new ArrayList<String>();

	    //Parse JSON data
	    try{
	    	JSONObject json = new JSONObject(response);
	    	JSONObject query = json.getJSONObject("query"); 
	    	JSONObject results = query.getJSONObject("results"); 
	    	JSONArray industries = results.getJSONArray("industry"); 
	    	//Save industries
	        for (int i = 0; i < industries.length(); i++) { 
	        	JSONObject industry = industries.getJSONObject(i); 
	        	
		        HibernateSession.executeSQL("INSERT INTO sector(ID,NAME) VALUES("+industry.getInt("id")+",'"+industry.getString("name")+"')");
	        	
	            //For each industries get companies
	            try{
		            JSONArray companies = industry.getJSONArray("company");         
		            for(int j=0; j < companies.length(); j++){
		            	JSONObject company = companies.getJSONObject(j);
		            	symbols.add(company.getString("symbol"));
		            	if(symbols.size()>=30){
			            	setStockExchange(industry.getInt("id"), symbols.toString());
			            	symbols.clear();
		            	}
		            }       
	            }catch(JSONException je){
	    	    	je.printStackTrace();
	    	    }
	        } 
	    }catch(JSONException je){
	    	je.printStackTrace();
	    }
	}
	
	/**
	 * Save company per sector
	 * 
	 * @param sectorId the sector id
	 * @param symbols a string that contains all sector's symbols separated by ","
	 */
	private void setStockExchange(int sector, String symbols){
		
		String symbolsTmp = symbols.substring(1, symbols.length()-1);
		symbolsTmp = symbolsTmp.replaceAll(" ", "");
		String symbolId;
		
		//YQL to get stock stockexchange
		String response = executeYQL("http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quotes%20where%20symbol%20in%20(%22"+ symbolsTmp + "%22)&format=json&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys");
		
		try{
			//Parse JSON data
	    	JSONObject json = new JSONObject(response);
	    	JSONObject query = json.getJSONObject("query"); 
	    	JSONObject results = query.getJSONObject("results");
	    	JSONArray quotes = results.getJSONArray("quote"); 
	    
			for(int i=0; i < quotes.length(); i++){
				try{
					JSONObject quote = quotes.getJSONObject(i);
		    		symbolId = quote.getString("StockExchange") + ":" + quote.getString("symbol"); 
		    		
		    		Float shortRatio = (float) ((quote.get("ShortRatio").equals(null)) ? 0.0 : Float.parseFloat(quote.get("ShortRatio").toString()));
		    		Float PERatio = (float) ((quote.get("PERatio").equals(null)) ? 0.0 : Float.parseFloat(quote.get("PERatio").toString()));
		    		Float PEGRatio = (float) ((quote.get("PEGRatio").equals(null)) ? 0.0 : Float.parseFloat(quote.get("PEGRatio").toString()));
		    			
					HibernateSession.executeSQL("INSERT into company(SYMBOL, NAME, 52_WEEK_LOW, 52_WEEK_HIGH, PE_RATIO, PEG_RATIO, SHORT_RATIO, BOOK_VALUE_RATIO) " +
		    				"VALUES('"+symbolId+"','"+ quote.getString("Name")+"',"+ 
		    				Float.parseFloat(quote.get("YearLow").toString())+","+ 
		    				Float.parseFloat(quote.get("YearHigh").toString())+","+ 
		    				PERatio+","+ 
		    				PEGRatio+","+
		    				shortRatio+","+ 
		    				Float.parseFloat(quote.get("BookValue").toString())+")");					
					HibernateSession.executeSQL("INSERT into comp_sec VALUES("+sector+",'"+ symbolId+"')");
				}catch(JSONException je){
			    	je.printStackTrace();
			    }
	    	}
		}catch(JSONException je){
	    	je.printStackTrace();
	    }
	}
	
	/**
	 * Get daily quote data such as Low, High, Price, Date, Volume. YQL can only
	 * use a few symbol for the resquest so it needs parameters.
	 * 
	 * @param min minimun of the range of company
	 * @param num number of compagny wanted
	 */
	public void getDailyData(int min, int count){
		
		Session session = HibernateSession.getSessionFactory().openSession();
		
		
		String symbolsTmp = ""; 
		String tmp = "";
		String [] fullSymbols = null;
		
		try{
			session.beginTransaction();
			//Get 100 companies to add quote
			Query hql = session.createQuery("SELECT symbol FROM Company");
			hql.setFirstResult(min);
			hql.setMaxResults(count);
			List<?> companies = hql.list();
			session.getTransaction().commit();
			
			//Make the correct string company list for YQL
			fullSymbols = new String[companies.size()]; 
			for(int i=0; i < companies.size(); i++){
				tmp = companies.get(i).toString();
				fullSymbols[i] = tmp;
				symbolsTmp = symbolsTmp + tmp.split(":")[1] + ","; 
			}
			symbolsTmp = symbolsTmp.substring(0, symbolsTmp.length()-1);
			
		}catch(HibernateException e){
			e.printStackTrace();
		}finally{
			session.flush();
		}
		
		
		//YQL to get stock stockexchange
		String response = executeYQL("http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quoteslist%20where%20symbol%20in%20(%22"+ symbolsTmp + "%22)&format=json&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys");
		
		//Parse JSON data
		try{
			Date date = new Date();
	    	JSONObject json = new JSONObject(response);
	    	JSONObject query = json.getJSONObject("query"); 
	    	JSONObject results = query.getJSONObject("results");
	    	JSONArray quotes = results.getJSONArray("quote"); 
	    	
	    	session.beginTransaction();
			for(int i=0; i < quotes.length(); i++){
				JSONObject quote = quotes.getJSONObject(i);
									
				session.createSQLQuery("INSERT INTO quote (SYMBOL, DATE, PRICE, DAILY_LOW, DAILY_HIGH, VOLUME) VALUES ('" + 
	    				fullSymbols[i] +"','"+ outputDateFormat.format(date) +"',"+ quote.getDouble("LastTradePriceOnly") 
	    				+","+ quote.get("DaysLow") +","+ quote.get("DaysHigh")  +","+ quote.get("Volume") +")").executeUpdate();
	    	}
			session.getTransaction().commit();
		
		}catch(JSONException je){
	    	je.printStackTrace();
	    }catch(HibernateException e){
			e.printStackTrace();
		}finally{
			session.flush();
			session.close();
		}
		
	}
	
	/**
	 * Execute YQL method to get financial data
	 * 
	 * @param url : the yahoo YQL url provide by : 
	 * http://developer.yahoo.com/yql/console/?q=show tables&env=store://datatables.org/alltableswithkeys
	 * 
	 * @return the response in JSON format
	 * 
	 */
	private String executeYQL(String url){
		
		HttpClient httpclient = new DefaultHttpClient();
		try{
			HttpGet httpget = new HttpGet(url);
			ResponseHandler<String> responseHandler = new BasicResponseHandler();
			return httpclient.execute(httpget, responseHandler);
		}
		catch(Exception e){
			e.printStackTrace();
			return "";
		}finally {
            // When HttpClient instance is no longer needed,
            // shut down the connection manager to ensure
            // immediate deallocation of all system resources
            httpclient.getConnectionManager().shutdown();
        }
		
	}
	
	
	public static void main(String[] args) throws Exception {
		GetRawData g = new GetRawData();
		switch(Integer.parseInt(args[0])){
			case 0 :
				//Get industry and company per industry
				System.out.println("Sectors and company data...");
				g.getIndustries();
				System.out.println("Sectors and company done");
				break;
			case 1 :
				System.out.println("Get daily data...");
				//FIXME faire sans exception
				try{
					//Get daily data 100 by 100 until 3000 is reached (there are less than 3000 companies in yahoo db)
					for(int i=0; i<=30; i++){
						g.getDailyData(i*100,100);
					}
				}catch(StringIndexOutOfBoundsException e){
					System.out.println("Daily data done");
				}
				break;
		}



	}
	


}
